using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using ShopForWeb.Model.Basic;
using ShopForWeb.Model.DBContexts;
using ShopForWeb.Model.View;

namespace ShopForWeb.Services
{
  public class CategoryService
  {
    //控制反转
    private ShopDBContext _db;//依赖
    public CategoryService(ShopDBContext db)//注入
    {
      _db = db;
    }
    public async Task<int> AddNewAsync(Category category)
    {
      //var entity = _db.Entry<Category>(category);
      //entity.State = EntityState.Added;
      _db.CategorySet.Add(category);
      return await _db.SaveChangesAsync();
    }
    public async Task<int> ModifyAsync(Category category)
    {
      _db.Entry<Category>(category).State = EntityState.Modified;
      return await _db.SaveChangesAsync();
    }
    public async Task<int> RemoveAsync(int categoryId)
    {
      var entity = _db.Entry<Category>(new Category()
      { CategoryId = categoryId });
      entity.State = EntityState.Deleted;
      return await _db.SaveChangesAsync();
    }

    //查询
    public async Task<List<CategoryView>> GetAllAsync()
    {
      IQueryable<CategoryView> query = _db.ProductSet
                                          .Include(p=>p.OwnCaregory)
                                          .GroupBy(p=>new{p.CategoryId,p.OwnCaregory.CategoryName})
                                          .Select(g=>new CategoryView()
                                          {
                                            CategoryId=g.Key.CategoryId,
                                            CategoryName = g.Key.CategoryName,
                                            Count = g.Count()
                                          });



        return await query.ToListAsync();
    }
    public async Task<List<CategoryView>> GetAllBysqlAsync(int statuCode)
    {
      var query=_db.Database.SqlQuery<TempCategory>($"""
       select a.categoryid,a.categoryName,b.productId,b.statuscode
         from t_category a
       left  join t_product b on a.categoryid=b.categoryid
       """)
       .GroupBy(c=> new {c.CategoryId,c.CategoryName})
         .Select(g=>new CategoryView()
         {
          CategoryId=g.Key.CategoryId,
          CategoryName = g.Key.CategoryName,
          Count = g.Count(c=> c.ProductId>0)
         });
        return await query.ToListAsync();
    }
   public async Task<List<CategoryView>> GetAllBySql0(int statusCode)
   {
    var query=_db.Database.SqlQuery<CategoryView>($"""
    select *,[count]=(select count(*)
                        from T_Product b
                        where a.CategoryId=b.CategoryId and b.StatusCode={statusCode} or {statusCode}=0)
             from T_Category a
    """);
    return await query.ToListAsync();
   
  }
    public async Task<List<CategoryView>> GetAllBySqlRawAsync(int statusCode)
    {
       var query=_db.Database.SqlQueryRaw<CategoryView>("""
    select *,[count]=(select count(*)
                        from T_Product b
                        where a.CategoryId=b.CategoryId and b.StatusCode={1} or {2}=0)
             from T_Category a
    """,[statusCode,statusCode]);
    return await query.ToListAsync();
    }
    public class TempCategory
    {
      public long CategoryId {get;set;}
      public string CategoryName{get;set;}="";
      public long ProductId{get;set;}
      public int StatusCode{get;set;}
    }
}
}